CentOS 7
Sponsored Link

SQL Server 2017 : Use from C#
2017/10/30
 
This is an example to use SQL Server from C#.
[1] This example is based on the environment that Microsoft .NET Core 2.0 has been installed yet like here.
[root@dlp ~]#
dotnet new console -o MssqlTest

The template "Console Application" was created successfully.

Processing post-creation actions...
Running 'dotnet restore' on MssqlTest/MssqlTest.csproj...
  Restoring packages for /home/cent/MssqlTest/MssqlTest.csproj...
  Generating MSBuild file /home/cent/MssqlTest/obj/MssqlTest.csproj.nuget.g.props.
  Generating MSBuild file /home/cent/MssqlTest/obj/MssqlTest.csproj.nuget.g.targets.
  Restore completed in 202.44 ms for /home/cent/MssqlTest/MssqlTest.csproj.

Restore succeeded.
[cent@dlp ~]$
cd MssqlTest

[cent@dlp MssqlTest]$
vi MssqlTest.csproj
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>
  # add
  <ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.4.0" />
  </ItemGroup>
</Project>
[2] Create a sample User and Database for Test.
[cent@dlp ~]$
sqlcmd -S localhost -U SA

Password:
# create login user
1> create login cent with PASSWORD= N'password';
2> go

# create [SampleDB]
1> create database SampleDB;
2> go

1> use SampleDB;
2> go
Changed database context to 'SampleDB'.

# create DB user
1> create user cent for login cent;
2> go

# asign DB owner role to [cent]
1> exec sp_addrolemember 'db_owner', 'cent';
2> go

# create [SampleTable]
1> create table SampleTable (
2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50)
3> );
4> insert into SampleTable (
5> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux'
6> );
7> go
[3] There are some basic usage to connect to SQL Server from C#.
[cent@dlp ~]$
cd MssqlTest

[cent@dlp MssqlTest]$
vi Program.cs
using System;
using System.Text;
using System.Data.SqlClient;

namespace SqlServerSample
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = "127.0.0.1";
                builder.UserID = "cent";
                builder.Password = "password";
                builder.InitialCatalog = "SampleDB";

                Console.Write("Connecting to SQL Server... ");
                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    connection.Open();
                    Console.WriteLine("Done.");
                    StringBuilder sb = new StringBuilder();

                    // Select from SampleTable
                    Console.WriteLine("Reading data from SampleTable...");
                    String sql = "select * from SampleTable;";
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine(
                                    "{0} {1} {2}", 
                                    reader.GetInt32(0),
                                    reader.GetString(1),
                                    reader.GetString(2)
                                    );
                            }
                        }
                    }
                    
                    // Insert from SampleTable
                    Console.Write("\r\nInserting into SampleTable...\r\n");
                    sb.Clear();
                    sb.Append("insert SampleTable (First_Name, Last_Name) ");
                    sb.Append("values (@first_name, @last_name);");
                    sql = sb.ToString();
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@first_name", "Ubuntu");
                        command.Parameters.AddWithValue("@last_name", "Linux");
                        int rowsAffected = command.ExecuteNonQuery();
                        Console.WriteLine(rowsAffected + " row(s) inserted");
                    }

                    // Update from SampleTable
                    String userToUpdate = "Redhat";
                    Console.Write("\r\nUpdating 'Last_Name' for user " + userToUpdate + "\r\n");
                    sb.Clear();
                    sb.Append("update SampleTable set Last_Name = N'Maipo' where First_Name = @first_name");
                    sql = sb.ToString();
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@first_name", userToUpdate);
                        int rowsAffected = command.ExecuteNonQuery();
                        Console.WriteLine(rowsAffected + " row(s) updated\r\n");
                    }
                    
                    sql = "select * from SampleTable;";
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine(
                                    "{0} {1} {2}",
                                    reader.GetInt32(0),
                                    reader.GetString(1),
                                    reader.GetString(2)
                                    );
                            }
                        }
                    }
                    
                    // Delete from SampleTable
                    String userToDelete = "Ubuntu";
                    Console.Write("\r\nDeleting user '" + userToDelete + "'\r\n");
                    sb.Clear();
                    sb.Append("delete from SampleTable where First_Name = @first_name;");
                    sql = sb.ToString();
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@first_name", userToDelete);
                        int rowsAffected = command.ExecuteNonQuery();
                        Console.WriteLine(rowsAffected + " row(s) deleted");
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}

[cent@dlp MssqlTest]$
dotnet restore

  Restoring packages for /home/cent/MssqlTest/MssqlTest.csproj...
  Restore completed in 30.92 ms for /home/cent/MssqlTest/MssqlTest.csproj.
[cent@dlp MssqlTest]$
dotnet run

Connecting to SQL Server... Done.
Reading data from SampleTable...
1 CentOS Linux
2 RedHat Linux
3 Fedora Linux

Inserting into SampleTable...
1 row(s) inserted

Updating 'Last_Name' for user Redhat
1 row(s) updated

1 CentOS Linux
2 RedHat Maipo
3 Fedora Linux
7 Ubuntu Linux

Deleting user 'Ubuntu'
1 row(s) deleted
 
Tweet